package com.echo.dao;

import com.echo.entity.Category;
import com.echo.util.JdbcUtils;
// 使用System.out代替Logger以减少依赖

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 分类DAO类，负责分类相关的数据库操作
 */
public class CategoryDao {
    // 移除Logger，使用System.out输出调试信息
    
    // 移除静态初始化块，避免在类加载时直接连接数据库
    
    /**
     * 添加分类 - 使用JdbcUtils获取连接
     */
    public int insert(Category category) {
        // 使用数据库实际字段名的SQL，与categories.sql表结构保持一致
        String sql = "INSERT INTO categories (category_name, category_desc, create_time, update_time, is_deleted) VALUES (?, ?, ?, ?, ?)";
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        
        try {
            System.out.println("[DEBUG] 开始执行分类插入操作");
            // 参数检查
            if (category == null) {
                System.out.println("[DEBUG] 分类对象为null");
                return 0;
            }
            
            if (category.getCategoryName() == null || category.getCategoryName().trim().isEmpty()) {
                System.out.println("[DEBUG] 分类名称为null或为空");
                return 0;
            }
            
            // 使用JdbcUtils获取连接，确保驱动正确加载
            conn = JdbcUtils.getConnection();
            System.out.println("[DEBUG] 数据库连接成功");
            
            // 创建PreparedStatement
            stmt = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
            System.out.println("[DEBUG] 创建PreparedStatement成功，SQL: " + sql);
            
            // 设置参数
            stmt.setString(1, category.getCategoryName());
            System.out.println("[DEBUG] 设置参数1 - category_name: " + category.getCategoryName());
            stmt.setString(2, category.getCategoryDesc() != null ? category.getCategoryDesc() : "");
            System.out.println("[DEBUG] 设置参数2 - category_desc: " + (category.getCategoryDesc() != null ? category.getCategoryDesc() : ""));
            stmt.setTimestamp(3, new java.sql.Timestamp(category.getCreateTime().getTime()));
            System.out.println("[DEBUG] 设置参数3 - create_time: " + category.getCreateTime());
            stmt.setTimestamp(4, new java.sql.Timestamp(category.getUpdateTime().getTime()));
            System.out.println("[DEBUG] 设置参数4 - update_time: " + category.getUpdateTime());
            stmt.setInt(5, category.getIsDeleted() != null ? category.getIsDeleted() : 0);
            System.out.println("[DEBUG] 设置参数5 - is_deleted: " + category.getIsDeleted());
            
            // 执行更新
            System.out.println("[DEBUG] 准备执行SQL更新");
            int rowsAffected = stmt.executeUpdate();
            System.out.println("[DEBUG] SQL更新执行完成，影响行数: " + rowsAffected);
            
            // 获取生成的ID
            if (rowsAffected > 0) {
                System.out.println("[DEBUG] 更新成功，影响行数>0，准备获取生成的ID");
                rs = stmt.getGeneratedKeys();
                if (rs != null && rs.next()) {
                    int generatedId = rs.getInt(1);
                    System.out.println("[DEBUG] 添加分类成功，ID: " + generatedId + "，名称: " + category.getCategoryName());
                    return generatedId;
                } else {
                    System.out.println("[DEBUG] 未获取到生成的ID");
                }
            } else {
                System.out.println("[DEBUG] 更新失败，影响行数=0");
            }
        } catch (SQLException e) {
            System.out.println("[DEBUG] 添加分类失败: " + e.getMessage());
            System.out.println("[DEBUG] SQL异常类型: " + e.getClass().getName());
            System.out.println("[DEBUG] SQL状态码: " + e.getSQLState());
            System.out.println("[DEBUG] SQL错误码: " + e.getErrorCode());
            e.printStackTrace();
        } finally {
            System.out.println("[DEBUG] 准备关闭数据库资源");
            // 使用JdbcUtils关闭资源
            JdbcUtils.close(conn, stmt, rs);
            System.out.println("[DEBUG] 数据库资源已关闭");
        }
        
        return 0;
    }
    
    /**
     * 更新分类
     */
    public int update(Category category) {
        String sql = "UPDATE categories SET category_name = ?, category_desc = ?, update_time = ? " +
                     "WHERE category_id = ? AND is_deleted = 0";
        Connection conn = null;
        PreparedStatement stmt = null;
        
        try {
            conn = JdbcUtils.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, category.getCategoryName());
            stmt.setString(2, category.getCategoryDesc() != null ? category.getCategoryDesc() : "");
            stmt.setTimestamp(3, new java.sql.Timestamp(category.getUpdateTime().getTime()));
            System.out.println("[DEBUG] 设置参数3 - update_time: " + category.getUpdateTime());
            stmt.setInt(4, category.getCategoryId());
            
            return stmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println("更新分类失败: " + e.getMessage());
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn, stmt, null);
        }
        return 0;
    }
    
    /**
     * 删除分类（物理删除）
     */
    public int delete(Integer categoryId) {
        String sql = "DELETE FROM categories WHERE category_id = ?";
        Connection conn = null;
        PreparedStatement stmt = null;
        
        try {
            conn = JdbcUtils.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, categoryId);
            
            return stmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println("删除分类失败: " + e.getMessage());
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn, stmt, null);
        }
        return 0;
    }
    
    /**
     * 根据ID查询分类
     */
    public Category findById(Integer categoryId) {
        String sql = "SELECT * FROM categories WHERE category_id = ? AND is_deleted = 0";
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        
        try {
            conn = JdbcUtils.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, categoryId);
            rs = stmt.executeQuery();
            
            if (rs.next()) {
                return mapResultSetToCategory(rs);
            }
        } catch (SQLException e) {
            System.out.println("根据ID查询分类失败: " + e.getMessage());
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn, stmt, rs);
        }
        return null;
    }
    
    /**
     * 查询所有分类
     */
    public List<Category> findAll() {
        String sql = "SELECT * FROM categories WHERE is_deleted = 0 ORDER BY category_id DESC";
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Category> categories = new ArrayList<>();
        
        try {
            conn = JdbcUtils.getConnection();
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();
            
            while (rs.next()) {
                categories.add(mapResultSetToCategory(rs));
            }
        } catch (SQLException e) {
            System.out.println("查询所有分类失败: " + e.getMessage());
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn, stmt, rs);
        }
        return categories;
    }
    
    /**
     * 查询所有分类（简化版本，不需要父分类概念）
     */
    public List<Category> findParentCategories() {
        // 由于数据库表没有parent_id字段，返回所有分类
        return findAll();
    }
    
    /**
     * 根据父分类ID查询子分类（由于数据库表没有parent_id字段，返回空列表）
     */
    public List<Category> findChildrenByParentId(Integer parentId) {
        // 由于数据库表没有parent_id字段，返回空列表
        return new ArrayList<>();
    }
    
    /**
     * 根据名称查询分类是否存在
     */
    public boolean existsByName(String categoryName) {
        String sql = "SELECT COUNT(*) FROM categories WHERE category_name = ? AND is_deleted = 0";
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        
        try {
            conn = JdbcUtils.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, categoryName);
            rs = stmt.executeQuery();
            
            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
        } catch (SQLException e) {
            System.out.println("查询分类名称是否存在失败: " + e.getMessage());
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn, stmt, rs);
        }
        return false;
    }
    
    /**
     * 将ResultSet映射为Category对象
     */
    private Category mapResultSetToCategory(ResultSet rs) throws SQLException {
        Category category = new Category();
        category.setCategoryId(rs.getInt("category_id"));
        category.setCategoryName(rs.getString("category_name"));
        category.setCategoryDesc(rs.getString("category_desc"));
        category.setCreateTime(rs.getTimestamp("create_time"));
        category.setUpdateTime(rs.getTimestamp("update_time"));
        category.setIsDeleted(rs.getInt("is_deleted"));
        
        return category;
    }
}